package org.iteam.xlz.core.service.product;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import org.apache.commons.lang3.StringUtils;
import org.iteam.commons.utils.DateUtil;
import org.iteam.core.utils.SqlBuilder;
import org.iteam.db.BaseService;
import org.iteam.xlz.core.entity.product.Product;

public class ProductService extends BaseService<Product>{
	static ProductService instance;
	private List<Object> params=new ArrayList<Object>();
	private StringBuilder sql=null;
	
	private ProductService(){}
	
	public static ProductService getInstance(){
		if(null==instance){
			instance=new ProductService();
		}
		return instance;
	}
	
	public void UpAndDown(){
		try {
			String sql="UPDATE sale_prd_online SET isOnSale=1 WHERE isOnSale=0 AND NOW() BETWEEN saleStartTime AND saleEndTime)";
			int i=super.updates(sql);
			System.out.println("本次自动上架商品"+i+"条");
			sql="update sale_prd_online set isOnSale=0 where isOnSale=1 AND NOW() not BETWEEN saleStartTime AND saleEndTime))";
			i=super.updates(sql);
			System.out.println("本次自动下架商品"+i+"条");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public boolean validate(String pid){
		if(StringUtils.isBlank(pid)){
			return false;
		}else{
			char[] array=pid.toCharArray();
			for(char s:array){
				byte b=(byte)s;
				if(b>=48&&b<=57){
					continue;
				}else
					if(b>=65&&b<=90){
						continue;
					}else
						if(b>=97&&b<=122){
							continue;
						}else
							return false;
			}
			return true;
		}
	}
	
	public List<Product> getActiveProduct(){
		try {
			return super.query("select * from sale_prd_inventory where 1=1 and status=1");
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<Product>();
		}
	}
	
	public List<Product> getOnTerminalProduct(String serialNo){
		try {
			return super.query("select * from sale_prd_inventory WHERE productId IN (SELECT l.productId FROM sale_teminal_channel_product l"
					+ " left join base_terminal t on l.terminalId = t.tid left join sale_channels a on t.tid = a.teminalId "
					+ " l WHERE 1=1 and t.serialNo = ? AND a.status=1 group by t.tid,l.productId) AND STATUS=1;",serialNo);
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<Product>();
		}
	}
	
	public long counts(String productname,String pid,String ptid,String parent,String leftCreateTime,String rightCreateTime){
		params.clear();
		sql=new StringBuilder("select count(1) from product where 1=1");
		if(StringUtils.isNotBlank(productname)){
			sql.append(" and productname like ?");
			params.add("%"+productname+"%");
		}
		if(StringUtils.isNotBlank(pid)){
			sql.append(" and pid=?");
			params.add(pid);
		}
		if(StringUtils.isNotBlank(leftCreateTime)&&StringUtils.isNotBlank(rightCreateTime)){
			sql.append(" and createtime between ? and ?");
			params.add(leftCreateTime);
			params.add(rightCreateTime);
		}
		if(StringUtils.isNotBlank(ptid)){
			sql.append(" and ptid=?");
			params.add(ptid);
		}else
			if(StringUtils.isNotBlank(parent)){
				sql.append(" and ptid in (select ptid from producttype where typeparent=?)");
				params.add(parent);
			}
		try {
			return super.counts(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	public List<Product> query(String productname,String pid,String ptid,String parent,String leftCreateTime,String rightCreateTime,long page,long rows){
		params.clear();
		sql=new StringBuilder("select * from product where 1=1");
		if(StringUtils.isNotBlank(productname)){
			sql.append(" and productname like ?");
			params.add("%"+productname+"%");
		}
		if(StringUtils.isNotBlank(pid)){
			sql.append(" and pid=?");
			params.add(pid);
		}
		if(StringUtils.isNotBlank(leftCreateTime)&&StringUtils.isNotBlank(rightCreateTime)){
			sql.append(" and createtime between ? and ?");
			params.add(leftCreateTime);
			params.add(rightCreateTime);
		}
		if(StringUtils.isNotBlank(ptid)){
			sql.append(" and ptid=?");
			params.add(ptid);
		}else
			if(StringUtils.isNotBlank(parent)){
				sql.append(" and ptid in (select ptid from producttype where typeparent=?)");
				params.add(parent);
			}
		sql.append(" order by createtime desc limit ?,?");
		params.add((page-1)*rows);
		params.add(rows);
		try {
			return super.query(sql.toString(),params.toArray());
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<Product>();
		}
	}
	
	public boolean create(Product model){
		model.setCreatetime(DateUtil.currentTime());
		
		SqlBuilder builder=SqlBuilder.createInsert("product");
		builder.addField("pid",model.getPid());
		builder.addField("ptid",model.getPtid());
		builder.addField("productname",model.getProductname());
		builder.addField("productamount",model.getProductamount());
		builder.addField("salesamount",model.getSalesamount());
		builder.addField("quantifier",model.getQuantifier());
		builder.addField("shippingfee",model.getShippingfee());
		builder.addField("isshowplaceinfo",model.getIsshowplaceinfo());
		builder.addField("productstatus",1);
		if(StringUtils.isNotBlank(model.getProductintro())){
			builder.addField("productintro",model.getProductintro());
		}
		if(StringUtils.isNotBlank(model.getProductdesc())){
			builder.addField("productdesc",model.getProductdesc());
		}
		builder.addField("isrecommend",model.getIsrecommend());
		if(StringUtils.isNotBlank(model.getRecommenddate())){
			builder.addField("recommenddate",model.getRecommenddate());
		}
		if(StringUtils.isNotBlank(model.getSharetitle())){
			builder.addField("sharetitle",model.getSharetitle());
		}
		builder.addField("attention",model.getAttention());
		if(StringUtils.isNotBlank(model.getSharedesc())){
			builder.addField("sharedesc",model.getSharedesc());
		}
		builder.addField("status",model.getStatus());
		builder.addField("createtime",model.getCreatetime());
		builder.addField("creator",model.getCreator());
		try {
			return super.updates(builder.getSql(),builder.getParams())>0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean update(Product model){
		SqlBuilder builder=SqlBuilder.createUpdate("product");
		
		builder.addField("ptid",model.getPtid());
		builder.addField("productname",model.getProductname());
		builder.addField("productamount",model.getProductamount());
		builder.addField("salesamount",model.getSalesamount());
		builder.addField("shippingfee",model.getShippingfee());
		builder.addField("isshowplaceinfo",model.getIsshowplaceinfo());
		builder.addField("quantifier",model.getQuantifier());
		if(StringUtils.isNotBlank(model.getProductintro())){
			builder.addField("productintro",model.getProductintro());
		}
		if(StringUtils.isNotBlank(model.getProductdesc())){
			builder.addField("productdesc",model.getProductdesc());
		}	
		builder.addField("isrecommend",model.getIsrecommend());
		if(StringUtils.isNotBlank(model.getRecommenddate())){
			builder.addField("recommenddate",model.getRecommenddate());
		}
		if(StringUtils.isNotBlank(model.getSharetitle())){
			builder.addField("sharetitle",model.getSharetitle());
		}
		builder.addField("attention",model.getAttention());
		if(StringUtils.isNotBlank(model.getSharedesc())){
			builder.addField("sharedesc",model.getSharedesc());
		}
		builder.addField("status",model.getStatus());
		builder.addWhere("pid",model.getPid());
		try {
			return super.updates(builder.getSql(),builder.getParams())>0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public Product findOne(String pid){
		try {
			return super.show("select * from product where 1=1 and pid=?",pid);
		} catch (SQLException e) {
			e.printStackTrace();
			return new Product();
		}
	}
	
	public boolean delete(String pid){
		try {
			return super.updates("delete from product where pid=?",pid)>0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public String game(int count){
        StringBuffer sb = new StringBuffer();
        String str = "0123456789";
        Random r = new Random();
        for(int i=0;i<count;i++){
            int num = r.nextInt(str.length());
            sb.append(str.charAt(num));
            str = str.replace((str.charAt(num)+""), "");
        }
        return sb.toString();
    }
	
	/**
	 * 获取待上架商品列表
	 */
	public List<Product> getOnGroundProduct(){
		try {
			return super.query("select * from product where status=0 and productstatus=1");
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<Product>();
		}
	}
}
